﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Speech.Synthesis;
using System.Speech;
using System.Speech.Recognition;

namespace WindowsFormsApp1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            this.timer2.Interval = 1000 * 60 * 1;  //5分钟
            this.timer2.Enabled = true;
            this.timer2.Tick += Timer2_Tick;
            this.txtCardNo.KeyDown += TxtCardNo_KeyDown;
            this.txtCardNo.TextChanged += TxtCardNo_TextChanged;
            lblFirstPerson.Text = lblFirstFood.Text = lblSecondFood.Text = lblSecondPerson.Text = lblThirdFood.Text = lblThirdPerson.Text = "";
            QueryCanShu();
        }

        private void Timer2_Tick(object sender, EventArgs e)
        {
            QueryCanShu();
        }

        private void TxtCardNo_TextChanged(object sender, EventArgs e)
        {
            if (txtCardNo.Text.Length > 16)
            {
                txtCardNo.Text = "";
                txtCardNo.Focus();
            }
        }

        private void TxtCardNo_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {

                if (!(txtCardNo.Text.Length == 8 || txtCardNo.Text.Length == 16))
                {
                    txtCardNo.Text = "";
                    txtCardNo.Focus();
                    Speak("卡号不正确");
                    lblMessage.Text = "卡号不正确";
                    return;
                }

                DateTime dtMorningFrom = Convert.ToDateTime("07:00");
                DateTime dtMorningTo = Convert.ToDateTime("08:20");
                DateTime dtNoonFrom = Convert.ToDateTime("11:00");
                DateTime dtNoonTo = Convert.ToDateTime("12:30");
                DateTime dtEveningFrom = Convert.ToDateTime("17:00");
                DateTime dtEveningTo = Convert.ToDateTime("18:30");
                DateTime dtNightFrom = Convert.ToDateTime("00:00");
                DateTime dtNightTo = Convert.ToDateTime("02:00");

                txtCardNo.Visible = false;
                string sCardInfo = txtCardNo.Text;
                txtCardNo.Text = "";
                lblMessage.Text = "";
                string type = string.Empty;

                if (DateTime.Now >= dtMorningFrom && DateTime.Now <= dtMorningTo)
                {
                    type = "0";
                }
                else if (DateTime.Now >= dtNoonFrom && DateTime.Now <= dtNoonTo)
                {
                    type = "1";
                }
                else if (DateTime.Now >= dtEveningFrom && DateTime.Now <= dtEveningTo)
                {
                    type = "2";
                }
                else if (DateTime.Now >= dtNightFrom && DateTime.Now <= dtNightTo)
                {
                    type = "3";
                }

                if (string.IsNullOrEmpty(type))
                {
                    Speak("非就餐时间");
                    lblMessage.Text = "非就餐时间";                    
                    txtCardNo.Visible = true;
                    txtCardNo.Focus();
                    return;
                }

                var connString = System.Configuration.ConfigurationManager.ConnectionStrings["connString"].ToString();
                using (MySqlConnection connection = new MySqlConnection(connString))
                {
                    try
                    {
                        connection.Open();
                        string sql = "";
                        if (sCardInfo.Length == 16)
                        {
                            sql = string.Format(@"select t1.`name`, t3.product_name from users as t1 join orders as t2 on t1.id = t2.user_id join order_items as t3 on t3.order_id = t2.id
                                    where TO_DAYS(t2.date) -TO_DAYS(NOW()) = 0 and t2.no = '{0}' and t2.`status` = '2' and t2.type = '{1}' ", sCardInfo, type);
                        }
                        else
                        {
                            sql = string.Format(@"select t1.`name`, t3.product_name from users as t1 join orders as t2 on t1.id = t2.user_id join order_items as t3 on t3.order_id = t2.id
                                    where TO_DAYS(t2.date) -TO_DAYS(NOW()) = 0 and t1.card_no = '{0}' and t2.`status` = '2' and t2.type = '{1}' ", sCardInfo, type);
                        }

                        MySqlCommand cmd = new MySqlCommand(sql, connection);
                        DataTable dt = new DataTable();
                        MySqlDataAdapter sqlDataAdapter = new MySqlDataAdapter(cmd);
                        sqlDataAdapter.Fill(dt);
                        if (dt.Rows.Count > 0)
                        {

                            Speak("已刷卡");
                            lblMessage.Text = "已刷卡";
                            return;
                        }

                        if (sCardInfo.Length == 16)
                        {
                            sql = string.Format(@"select t1.`name`, t3.product_name from users as t1 join orders as t2 on t1.id = t2.user_id join order_items as t3 on t3.order_id = t2.id
                                    where TO_DAYS(t2.date) -TO_DAYS(NOW()) = 0 and t2.no = '{0}' and t2.`status` = '1' and t2.type = '{1}' ", sCardInfo, type);
                        }
                        else
                        {
                            sql = string.Format(@"select t1.`name`, t3.product_name from users as t1 join orders as t2 on t1.id = t2.user_id join order_items as t3 on t3.order_id = t2.id
                                    where TO_DAYS(t2.date) -TO_DAYS(NOW()) = 0 and t1.card_no = '{0}' and t2.`status` = '1' and t2.type = '{1}' ", sCardInfo, type);
                        }
                        cmd = new MySqlCommand(sql, connection);
                        dt = new DataTable();
                        sqlDataAdapter = new MySqlDataAdapter(cmd);
                        sqlDataAdapter.Fill(dt);
                        if (dt.Rows.Count > 0)
                        {
                            lblThirdPerson.Text = lblSecondPerson.Text;
                            lblThirdFood.Text = lblSecondFood.Text;
                            lblSecondPerson.Text = lblFirstPerson.Text;
                            lblSecondFood.Text = lblFirstFood.Text;
                            lblFirstFood.Text = "";
                            int index = 0;
                            foreach (DataRow dr in dt.Rows)
                            {
                                index++;
                                lblFirstPerson.Text = dr[0].ToString();
                                lblFirstFood.Text += dr[1].ToString();
                                if (index % 3 == 0)
                                {
                                    lblFirstFood.Text += System.Environment.NewLine;
                                }
                                else
                                {
                                    lblFirstFood.Text += " | ";
                                }
                            }
                            Speak("请领餐");
                            lblMessage.Text = "请领餐";
                            if (sCardInfo.Length == 16)
                            {
                                sql = string.Format(@"update orders set status = '2',updated_at = current_timestamp where TO_DAYS(date) -TO_DAYS(NOW()) = 0
                                and type = '{0}' and status = '1' and no = '{1}'", type, sCardInfo);
                            }
                            else
                            {
                                sql = string.Format(@"update orders set status = '2',updated_at = current_timestamp where TO_DAYS(date) -TO_DAYS(NOW()) = 0
                                and type = '{0}' and status = '1' and user_id IN (select id from users where card_no = '{1}') ", type, sCardInfo);
                            }

                            cmd = new MySqlCommand(sql, connection);
                            cmd.ExecuteNonQuery();
                            QueryCanShu();
                        }
                        else
                        {
                            Speak("没有订餐");
                            lblMessage.Text = "没有订餐";
                        }                      
                    }
                    catch (MySqlException exception)
                    {
                        MessageBox.Show(exception.Message);
                    }
                    finally
                    {
                        txtCardNo.Visible = true;
                        txtCardNo.Focus();
                    }
                }
            }
        }

        private void QueryCanShu()
        {
            DateTime dtNightFrom = Convert.ToDateTime("00:30");
            DateTime dtNightTo = Convert.ToDateTime("02:00");
            var connString = System.Configuration.ConfigurationManager.ConnectionStrings["connString"].ToString();
            string queryDinnerString = string.Empty;
            
            if (DateTime.Now >= dtNightFrom && DateTime.Now <= dtNightTo)
            {
                queryDinnerString = "select type, count(*) as total from orders where TO_DAYS(date) - TO_DAYS(NOW()) =0 AND `status`='1' AND type = '3' GROUP BY type";
            }
            else
            {
                queryDinnerString = "select type, count(*) as total from orders where TO_DAYS(date) - TO_DAYS(NOW()) =1 AND `status`='1' AND type = '3' GROUP BY type";
            }

            using (MySqlConnection connection = new MySqlConnection(connString))
            {
                try
                {
                    connection.Open();
                    string sql = "select type, count(*) as total from orders where TO_DAYS(date) - TO_DAYS(NOW()) =0 " +
                        "AND `status`='1' AND type IN ('0','1','2') GROUP BY type UNION " + queryDinnerString;
                    MySqlCommand cmd = new MySqlCommand(sql, connection);
                    MySqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())//初始索引是-1，执行读取下一行数据，返回值是bool
                    {
                        switch (reader[0].ToString())
                        {
                            case "0":
                                lblMorningCount.Text = reader[1].ToString();
                                break;
                            case "1":
                                lblNoonCount.Text = reader[1].ToString();
                                break;
                            case "2":
                                lblEveningCount.Text = reader[1].ToString();
                                break;
                            case "3":
                                lblNightCount.Text = reader[1].ToString();
                                break;
                            default:
                                break;
                        }
                    }
                }
                catch (MySqlException exception)
                {
                    MessageBox.Show(exception.Message);
                }
            }
        }

        private void Speak(string content)
        {
            SpeechSynthesizer ssh = new SpeechSynthesizer
            {  //音量控制(0~100)
                Volume = 100,

                //语速控制(-10,10)
                Rate = 1,
            };

            //选择语音播放人的性别与年龄
            ssh.SelectVoiceByHints(VoiceGender.Female, VoiceAge.Child);

            //语音输出(同步)
            //ssh.Speak(content);
            //语音输出(异步)
            ssh.SpeakAsync(content);
        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            lblTime.Text = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
        }

        private void label19_Click(object sender, EventArgs e)
        {

        }
    }
}
